<?php
 /******************************************************************************
 *   GunCMS is an avid supporter of open source software. This is the appropriate 
 *   option if you are creating an open source application with a license 
 *   compatible with the GNU GPL license v3.
 *   For details http://www.gnu.org/licenses/gpl.html
 *   DO NOT USE FOR COMMERCIAL WITHOUT PERMISSION
 *
 * GunCMS Version 1.0.3
 * Copyright(c) 2009-2010, T & L GdbR
 * info@guncms.de
 * 
 * http://www.guncms.de
 ********************************************************************************/
/**
 * product class. Provide all utilities function related to 
 * product table.
 * <u>Note:</u> all $db param in first parameter is ezMySQL object.
 * See ezSQL documentation for more information.
 */
class BWebshopProductBean {
    function __construct() {
    }
    
    /**
     * Validate product data
     */
    function validateProduct() {
    	$msg = "";	
    
		if($_POST['product_name'] == "") {
			if($msg != "") $msg .= "<br />";
			$msg .= _("Missing Product name.");
		}
		
		if(is_numeric($_POST['product_price']) == false) {
			if($msg != "") $msg .= "<br />";
			$msg .= _("Invalid product price, product price should be a numeric value.");
		}
		
		return $msg;
    }
 
    /**
     * Save product.
     * product is array with format. See smarty template file of product for
     * form's element name.
     * Ex: [cat_parent] => [model_number] => [product_name] => [product_thumbnail] => [product_price] => [description] => }
     */
    function saveProduct($db, $product) {
    	$res = $db->query(sprintf(
					"INSERT INTO %s(product_group_id, product_name, product_thumbnail, " .
					"product_price, currency_id, tax, transitdays, product_description, status, product_date_added) " .
					"VALUES(%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', now())",
    				TBL_PRODUCT, 
    				$product['product_group_id'],
    				$product['product_name'],
    				$product['product_thumbnail'],
    				$product['product_price'],
    				$product['currency_id'],
    				$product['tax'],
    				$product['transitdays'],
    				$product['product_description'],
    				$product['status']));
		if($res == true)
    		return $id = $db->insert_id;
    	else
    		return -1;
		
    }
	
	/**
     * Save photos of product.
     */
    function saveProductPhotos($db, $productId,$product_photo) {
    	$res = $db->query(sprintf(
					"INSERT INTO %s(product_id, product_photo, product_date_create) " .
					"VALUES(%s, '%s', now())",
    				TBL_PRODUCT_PHOTOS, 
    				$productId,
    			    $product_photo));
		if($res == true)
    		return $id = $db->insert_id;
    	else
    		return -1;
		
    }
    
	/**
     * Get a photos with inputted product id.
     * @param $id id of the product.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function PhotosByProductId($db, $id) {
    	$sql = "SELECT * FROM " . TBL_PRODUCT_PHOTOS . " p ".
				" WHERE p.product_id='$id'";
    			
    	$res = $db->get_results($sql);
    	return $res;
    }
	
	function deletePhotoProduct($db, $id) {
		//Delete product
    	$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.id = '$id'", 
				TBL_PRODUCT_PHOTOS);
		
		
    	$res = $db->query($sql);
  	
    	return $res;
	}
	
	/**
     * COUNT all products in database.
     * @return total of product.
     * Notes: this data is viewed in Overview.  
     */
	public function count_all_product($db) {
		$sql = sprintf("SELECT COUNT(product_id) as allproduct FROM ".TBL_PRODUCT);
		$res = $db->get_row($sql);
		return $res;				
	}
    /**
     * Get all products in database.
     * @return The list of product object.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getAllProduct($db,$sort,$by) {
    	if($sort!="") {
    		if ($sort=="product_group_name")
    			$condsort = "ORDER BY p.product_group_id ".$by;
    		else
				$condsort = "ORDER BY p.".$sort." ".$by;
    	} else {
			$condsort = "ORDER BY p.product_id ";
    	}	
    	$sql = sprintf("SELECT l.product_group_name, p.*, c.currency_id, c.symbol_left, c.code ".
						" FROM ".TBL_PRODUCT." p ".
						" LEFT JOIN ".TBL_PRODUCT_GROUP." l ON p.product_group_id = l.product_group_id ".
						" LEFT JOIN ".TBL_CURRENCY." c ON c.currency_id = p.currency_id ".
    					" $condsort ");
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
    function getAllProductsSharePage($db,$paging,$path,$sort,$by) {
    	if($sort!="") {
    		if ($sort=="product_group_name")
    			$condsort = "ORDER BY p.product_group_id ".$by;
    		else
				$condsort = "ORDER BY p.".$sort." ".$by;
    	} else {
			$condsort = "ORDER BY p.product_id ";
    	}	
		$page = isset ( $_GET[$path] ) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $paging;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
	
		$strSQL = "SELECT p.*,g.product_group_name, c.symbol_left, s.specials_new_products_price, f.featured_new_products_price FROM".
				" ((" . TBL_PRODUCT . " p JOIN " . TBL_CURRENCY . " c ON p.currency_id = c.currency_id) LEFT JOIN " . TBL_SPECIAL . " s ON s.product_id = p.product_id)".
				" LEFT JOIN " . TBL_PRODUCT_GROUP . " g ON g.product_group_id = p.product_group_id 	".
				" LEFT JOIN " . TBL_FEATURED . " f ON f.product_id = p.product_id  $condsort LIMIT $page_start, $rows_per_page";
		$res = $db->get_results($strSQL);
		return $res;
	}
    
    function getAllProducts($db) {
    	$sql = "SELECT p.*, c.symbol_left, s.specials_new_products_price, f.featured_new_products_price FROM".
				" ((" . TBL_PRODUCT . " p JOIN " . TBL_CURRENCY . " c ON p.currency_id = c.currency_id) LEFT JOIN " . TBL_SPECIAL . " s ON s.product_id = p.product_id)".
				" LEFT JOIN " . TBL_FEATURED . " f ON f.product_id = p.product_id";
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
    function getAllProductsSearch($db ,$txtKey) {
    	$sql = "SELECT p.*,g.product_group_name, c.symbol_left, s.specials_new_products_price, f.featured_new_products_price FROM".
				" ((" . TBL_PRODUCT . " p JOIN " . TBL_CURRENCY . " c ON p.currency_id = c.currency_id) LEFT JOIN " . TBL_SPECIAL . " s ON s.product_id = p.product_id)".
				" LEFT JOIN " . TBL_PRODUCT_GROUP . " g ON g.product_group_id = p.product_group_id 	".
				" LEFT JOIN " . TBL_FEATURED . " f ON f.product_id = p.product_id WHERE p.product_name like '%$txtKey%'";
    			
    	$res = $db->get_results($sql);
    	return $res;
    }
    
    function getAllProductsSearchSharePage($db ,$txtKey) {
    	$page = isset ( $_GET["list"] ) ? intval ( $_GET["list"] ) : 1;
		$rows_per_page= MAX_DEMO_PRODUCT;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
    	$sql = "SELECT p.*,g.product_group_name, c.symbol_left, s.specials_new_products_price, f.featured_new_products_price FROM".
				" ((" . TBL_PRODUCT . " p JOIN " . TBL_CURRENCY . " c ON p.currency_id = c.currency_id) LEFT JOIN " . TBL_SPECIAL . " s ON s.product_id = p.product_id)".
				" LEFT JOIN " . TBL_PRODUCT_GROUP . " g ON g.product_group_id = p.product_group_id 	".
				" LEFT JOIN " . TBL_FEATURED . " f ON f.product_id = p.product_id WHERE p.product_name like '%$txtKey%' ORDER BY p.product_id ASC LIMIT $page_start, $rows_per_page";
    			
    	$res = $db->get_results($sql);
    	return $res;
    }
    
    /**
     * Get a product with inputted product id.
     * @param $id id of the product.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getProduct($db, $id) {
    	$sql = "SELECT p.*, ca.cat_name, c.symbol_left FROM " . TBL_PRODUCT . " p ".
				"LEFT JOIN " . TBL_CURRENCY . " c ON c.currency_id = p.currency_id".
				" LEFT JOIN " . TBL_CATEGORY . " ca ON p.product_group_id = ca.cat_ID WHERE p.product_id='$id'";
    			
    	$res = $db->get_row($sql);
    	
    	return $res;
    }
    
  /**
   * @brief Get information of a given product
   * @param $id id of the product
   * 		$db connection to mysql		
   * @retval mysql-result
   * @date 2008/08/22
  **/
	function getProductForCustomer($db, $id) {
    	$sql = "SELECT p.*, ca.cat_name, c.symbol_left, f.featured_new_products_price, s.specials_new_products_price".
				" FROM " . TBL_CURRENCY . " c LEFT JOIN " . TBL_PRODUCT . " p ON c.currency_id = p.currency_id".
				" INNER JOIN " . TBL_CATEGORY . " ca ON p.product_group_id = ca.cat_ID".
				" LEFT JOIN " . TBL_FEATURED . " f ON f.product_id = p.product_id LEFT JOIN " . TBL_SPECIAL . " s ON s.product_id = p.product_id WHERE p.product_id='$id'";
    			
    	$res = $db->get_row($sql);
    	
    	return $res;
    }
   
  /**
   * @brief Get information of a given currency
   * @param $id id of the currency
   * 		$db connection to mysql		
   * @retval mysql-result
   * @date 2008/08/22
  **/
    function getCurrencyById($db, $id) {
    	$sql = "SELECT * FROM " . TBL_CURRENCY . " WHERE currency_id = '$id'";
    	$res = $db->get_row($sql);
    	
    	return $res;
    }
    
  /**
   * @brief Get all currencies in table " . TBL_CURRENCY . "
   * @param $db connection to mysql		
   * @retval mysql-result
   * @date 2008/08/22
  **/
    function getAllCurrency($db) {
    	$sql = "SELECT * FROM " . TBL_CURRENCY . " ORDER BY currency_id ";
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
  /**
   * @brief Get currency of a given product
   * @param $id id of the product
   * 		$db connection to mysql		
   * @retval mysql-result
   * @date 2008/08/22
  **/
    function getCurrencyByProductId($db, $id) {
    	$sql = "SELECT c.currency_id FROM " . TBL_CURRENCY . " c JOIN " . TBL_PRODUCT . " p ON c.currency_id = p.currency_id AND p.product_id = $id";
    	$res = $db->get_row($sql);
    	
    	return $res->currency_id;
    }
    
  /**
   * @brief Get products which exist in table " . TBL_PRODUCT . " but not exist in table " . TBL_SPECIAL . " and table " . TBL_FEATURED . "
   * @param $db connection to mysql		
   * @retval mysql-result
   * @date 2008/08/22
  **/
    function getProductGroup($db) {
    	$sql = "SELECT DISTINCT p.product_id, p.product_name, p.product_price , c.currency_id, c.symbol_left".
				" FROM " . TBL_PRODUCT . " p JOIN " . TBL_CURRENCY . " c ON p.currency_id = c.currency_id".
				" WHERE NOT EXISTS (SELECT s.product_id FROM " . TBL_SPECIAL . " s WHERE s.product_id = p.product_id)".
				" AND NOT EXISTS (SELECT f.product_id FROM " . TBL_FEATURED . " f WHERE f.product_id = p.product_id)";
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
    /**
     * Get all product with inputted category id.
     * @param $id the category id.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getProductsByCategory($db, $id) {
		$page = isset ( $_GET["list"] ) ? intval ( $_GET["list"] ) : 1;
		$rows_per_page= MAX_DEMO_PRODUCT;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
    	$sql = "SELECT p.*, c.symbol_left, f.featured_new_products_price, s.specials_new_products_price".
				" FROM ((" . TBL_PRODUCT . " p LEFT JOIN " . TBL_CURRENCY . " c ON p.currency_id = c.currency_id)".
				" LEFT JOIN " . TBL_FEATURED . " f ON p.product_id = f.product_id) LEFT JOIN " . TBL_SPECIAL . " s ON s.product_id = p.product_id".
				" WHERE p.product_group_id='$id' LIMIT $page_start, $page_end";
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
    /**
     * Get all product with condition string
     * @param $cond the condition string
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getProductsByCondition($db, $cond) {
    	$sql = sprintf("SELECT * FROM %s where ($cond)", 
    			TBL_PRODUCT);
    			
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
     /**
     * Delete a product with inputted id.
     * @param $id the product's id.
     * @return true if delete successfully otherwise return false.
     */
    function deleteProduct($db, $id) {
		//Delete product
    	$sql = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.product_id = '$id'", 
				TBL_PRODUCT);
		$sql1 = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.product_id = '$id'", 
				TBL_FEATURED);
		$sql2 = sprintf("DELETE FROM %1\$s" .
				" where %1\$s.product_id = '$id'", 
				TBL_SPECIAL);
		
    	$res = $db->query($sql);
    	$res1 = $res.$db->query($sql1);
    	$res2 = $res1.$db->query($sql2);
  	
    	return $res2;
	}
	
	 /**
     * Update a product.
     * @param $product is the $_POST param send by HTML form. 
     * See product HTML form in smarty template file for more about
     * form element' name.
     * @return true if query is execute successfully. Otherwise return false.
     */
	function updateProduct($db, $product) {
    	$res = $db->query(sprintf(
					"UPDATE %s SET " .
					"product_name 			= '%s', " .
					"product_group_id 		= '%s', " .
					"product_thumbnail		= '%s', " .
					"product_price			= '%s', " .
					"currency_id  			= '%s', " .
					"tax  					= '%s', " .
					"transitdays  			= '%s', " .
					"status					= '%s', " .
					"product_description	= '%s', " .
					"product_date_modified	= now() " .
					"WHERE product_id 		= '%s' ", 
    				TBL_PRODUCT,
					$product['product_name'],
					$product['product_group_id'],
					$product['product_thumbnail'],
					$product['product_price'],
					$product['currency_id'],
					$product['tax'],
					$product['transitdays'],
					$product['status'],
					$product['product_description'],
					$product['product_id']
					));
    	return $res;
    }
    
    /*
     * 
     */
     function getProductByGroupId ($db, $groupId) {
     	$sql = sprintf("SELECT *, pg.product_group_name FROM " . TBL_PRODUCT . " p".
     	" LEFT JOIN ".TBL_PRODUCT_GROUP." pg ON p.product_group_id = pg.product_group_id".
     	" WHERE p.product_group_id = '$groupId'");
     	$res = $db->get_results($sql);
     	return $res;
     }
	 
	 function getProductByGroupIdAssign ($db, $groupId,$catid) {
     	$sql = sprintf("SELECT p.product_id,p.product_name,p.product_thumbnail, g.product_group_name,".
			"(SELECT t.product_id FROM ".TBL_PRODUCT_GROUP2CAT." t WHERE t.product_id = p.product_id AND t.cat_id = '$catid' LIMIT 0,1) as active_product ".
			" FROM " . TBL_PRODUCT . " p".
     		" LEFT JOIN ".TBL_PRODUCT_GROUP." g ON p.product_group_id = g.product_group_id".
     		" WHERE p.product_group_id = '$groupId'");
     	$res = $db->get_results($sql);
     	return $res;
     }
     
}
?>